using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;

namespace Avanzis.QueryExecutor.OleDb
{
    /// <summary>
    /// Ejecuta las sentencias SQL para una base de datos OleDb (Access)
    /// </summary>
    public class OleDbQueryFrameworkQueryFrameworkProvider : QueryFrameworkQueryFrameworkProvider
    {
        /// <summary>
        /// Executes the dataset.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="commandType">Type of the command.</param>
        /// <param name="commandText">The command text.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public override DataSet ExecuteDataset(DbConnection connection, CommandType commandType, string commandText, params DbParameter[] parameters)
        {
            //create a command and prepare it for execution
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = (OleDbConnection)connection;
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;

            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(CreaterParameterArray(parameters));
            }

            //create the DataAdapter & DataSet
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();

            //fill the DataSet using default values for DataTable names, etc.
            da.Fill(ds);

            // detach the OleDbParameters from the command object, so they can be used again.			
            cmd.Parameters.Clear();

            //return the dataset
            return ds;
        }

        /// <summary>
        /// Executes the reader.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="commandType">Type of the command.</param>
        /// <param name="commandText">The command text.</param>
        /// <param name="externalConnection">if set to <c>true</c> [external connection].</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public override DbDataReader ExecuteReader(DbConnection connection, CommandType commandType, string commandText, bool externalConnection, params DbParameter[] parameters)
        {
            //create a command and prepare it for execution
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = (OleDbConnection)connection;
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;

            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(CreaterParameterArray(parameters));
            }

            //create a reader
            //create a reader
            OleDbDataReader dr;

            // call ExecuteReader with the appropriate CommandBehavior
            if (externalConnection)
            {
                dr = cmd.ExecuteReader();
            }
            else
            {
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }

            // detach the SqlParameters from the command object, so they can be used again.
            cmd.Parameters.Clear();

            return dr;
        }

        /// <summary>
        /// Executes the reader.
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="commandType">Type of the command.</param>
        /// <param name="commandText">The command text.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public override DbDataReader ExecuteReader(DbTransaction transaction, CommandType commandType, string commandText, params DbParameter[] parameters)
        {
            //create a command and prepare it for execution
            OleDbCommand cmd = new OleDbCommand();
            cmd.Transaction = (OleDbTransaction)transaction;
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;

            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(CreaterParameterArray(parameters));
            }

            //create a reader
            OleDbDataReader dr = cmd.ExecuteReader();

            // detach the SqlParameters from the command object, so they can be used again.
            cmd.Parameters.Clear();

            return dr;
        }


        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="commandType">Type of the command.</param>
        /// <param name="commandText">The command text.</param>
        /// <param name="parameters">The parameters.</param>
        public override int ExecuteNonQuery(DbConnection connection, CommandType commandType, string commandText, params DbParameter[] parameters)
        {
            //create a command and prepare it for execution
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = (OleDbConnection)connection;
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;

            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(CreaterParameterArray(parameters));
            }

            int result = cmd.ExecuteNonQuery();

            cmd.Parameters.Clear();

            return result;
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="transaction">The transaction.</param>
        /// <param name="commandType">Type of the command.</param>
        /// <param name="commandText">The command text.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public override int ExecuteNonQuery(DbTransaction transaction, CommandType commandType, string commandText, params DbParameter[] parameters)
        {
            //create a command and prepare it for execution
            OleDbCommand cmd = new OleDbCommand();
            cmd.Transaction = (OleDbTransaction)transaction;
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;

            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(CreaterParameterArray(parameters));
            }

            int result = cmd.ExecuteNonQuery();

            cmd.Parameters.Clear();

            return result;
        }

        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="commandType">Type of the command.</param>
        /// <param name="commandText">The command text.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public override object ExecuteScalar(DbConnection connection, CommandType commandType, string commandText, params DbParameter[] parameters)
        {
            //create a command and prepare it for execution
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = (OleDbConnection)connection;
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;

            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(CreaterParameterArray(parameters));
            }

            //execute the command & return the results
            object retval = cmd.ExecuteScalar();

            // detach the SqlParameters from the command object, so they can be used again.
            cmd.Parameters.Clear();

            return retval;
        }

        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="transaction">The transaction.</param>
        /// <param name="commandType">Type of the command.</param>
        /// <param name="commandText">The command text.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public override object ExecuteScalar(DbTransaction transaction, CommandType commandType, string commandText, params DbParameter[] parameters)
        {
            //create a command and prepare it for execution
            OleDbCommand cmd = new OleDbCommand();
            cmd.Transaction = (OleDbTransaction)transaction;
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;

            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(CreaterParameterArray(parameters));
            }

            //execute the command & return the results
            object retval = cmd.ExecuteScalar();

            // detach the SqlParameters from the command object, so they can be used again.
            cmd.Parameters.Clear();

            return retval;
        }

        /// <summary>
        /// Gets a closed connection to the Database
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <returns></returns>
        public override DbConnection CreateConnection(string connectionString)
        {
            return new OleDbConnection(connectionString);
        }

        public override DataSet ExecuteDataset(DbConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteDataset(connection, commandType, commandText, null);
        }

        public override DbDataReader ExecuteReader(DbConnection connection, CommandType commandType, string commandText, bool externalConnection)
        {
            return ExecuteReader(connection, commandType, commandText, externalConnection, null);
        }

        public override int ExecuteNonQuery(DbConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(connection, commandType, commandText, null);
        }

        public override object ExecuteScalar(DbConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteScalar(connection, commandType, commandText, null);
        }

        public override int ExecuteNonQuery(DbTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(transaction, commandType, commandText, null);
        }

        public override object ExecuteScalar(DbTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteScalar(transaction, commandType, commandText, null);
        }

        //public override DbParameter CreateParameter(DbParameter parameter)
        //{
        //    OleDbParameter returnedParameter = new OleDbParameter();

        //    returnedParameter.ParameterName = parameter.ParameterName;
        //    returnedParameter.DbType = parameter.DbType;
        //    returnedParameter.Value = parameter.Value;
        //    returnedParameter.Direction = parameter.Direction;

        //    if (parameter.Size > 0)
        //        returnedParameter.Size = parameter.Size;

        //    return returnedParameter;
        //}

        protected virtual OleDbParameter[] CreaterParameterArray(params DbParameter[] parameters)
        {
            if (parameters == null || parameters.Length == 0)
                return null;

            List<OleDbParameter> returnedParameters = new List<OleDbParameter>();

            if (parameters.Length > 0)
            {
                foreach (DbParameter queryParameter in parameters)
                {
                    returnedParameters.Add((OleDbParameter)queryParameter);
                }
            }
            return returnedParameters.ToArray();
        }

        public override DbCommand CreateCommand(string commandText, DbConnection connection)
        {
            return new OleDbCommand(commandText, (OleDbConnection)connection);
        }

        public override DbDataAdapter CreateDataAdapter(DbCommand command)
        {
            return new OleDbDataAdapter((OleDbCommand)command);
        }

        public override DbDataAdapter CreateDataAdapter(string commandText, DbConnection connection)
        {
            return new OleDbDataAdapter(commandText, (OleDbConnection)connection);
        }

        public override DbParameter CreateParameter(string parameterName, object parameterValue)
        {
            return new OleDbParameter(parameterName, parameterValue);
        }

        public override object ProcessDatabaseValue(object value)
        {
            return value;
        }
    }
}
